﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Text.RegularExpressions;
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using System.Diagnostics;

namespace iHub.App_Code
{
    class ExcelHandler
    {
        private object misValue = System.Reflection.Missing.Value;
        private Excel.Application xlApp = null;
        private Excel.Workbooks books = null;
        private Excel.Workbook xlWorkBook = null;
        private Excel.Sheets sheets = null;
        private Excel.Worksheet xlWorkSheet = null;
        private Excel.Range range = null;
        private int hWnd = 0;
        private AssignmentManager assm = new AssignmentManager();
        private AccountManager acctm = new AccountManager();
        private EmployeeManager en = new EmployeeManager();
        private PayrollManager paym = new PayrollManager();
        private StringValidator validator = new StringValidator();
        [DllImport("user32.dll")]
        private static extern uint GetWindowThreadProcessId(IntPtr hWnd, out uint lpdwProcessId);

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                throw new Exception(ex.ToString());
            }
            finally
            {
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }

        public static bool TryKillProcessByMainWindowHwnd(int hWnd)
        {
            uint processID;

            GetWindowThreadProcessId((IntPtr)hWnd, out processID);
            if (processID == 0) return false;
            try
            {
                Process.GetProcessById((int)processID).Kill();
            }
            catch (ArgumentException)
            {
                return false;
            }
            catch (Win32Exception)
            {
                return false;
            }
            catch (NotSupportedException)
            {
                return false;
            }
            catch (InvalidOperationException)
            {
                return false;
            }
            return true;
        }

        public static void KillProcessByMainWindowHwnd(int hWnd)
        {
            uint processID;
            GetWindowThreadProcessId((IntPtr)hWnd, out processID);
            if (processID == 0)
                throw new ArgumentException("Process has not been found by the given main window handle.", "hWnd");
            Process.GetProcessById((int)processID).Kill();
        }

        public void Print_MasterList(Boolean type, string id, string no)
        {
            DataTable dt = new DataTable();
            List<string> units = new List<string>();
            List<string> names = new List<string>();
            DataRow[] results;
            DataTable distinct = new DataTable();
            string t = "";
            string batch = "";
            xlApp = new Excel.ApplicationClass();
            books = xlApp.Workbooks;
            xlWorkBook = books.Add(misValue);
            sheets = xlWorkBook.Sheets;
            xlWorkSheet = (Excel.Worksheet)sheets.get_Item(1);
            hWnd = xlApp.Application.Hwnd;

            if (type)
            {
                t = "Reimbursement";
            }
            else
            {
                t = "masterlist";
            }

            if (no.Equals(""))
            {
                batch = "All Batches";
            }
            else
            {
                batch = "Batch" + no;
            }

            if (type)
            {
                try
                {
                    dt = assm.getrcbctable(id, no);
                    distinct = dt.DefaultView.ToTable(true, "Branch");
                    xlWorkSheet.Name = "Reimbursement -" + batch;
                    int count3 = 2;
                    int count4 = 1;
                    int count = 0;
                    float total = 0;
                    float grandtotal = 0;
                    int totalcount = 0;
                    printselect.formprint.toolStripProgressBar1.Value += 5;

                    for (int n = 0; distinct.Rows.Count > n; n++)
                    {
                        units.Add(Convert.ToString(distinct.Rows[n]["Branch"]));
                    }

                    xlWorkSheet.Cells[1, 1] = "Assign No.";
                    xlWorkSheet.Cells[1, 2] = "Date of Endorsement";
                    xlWorkSheet.Cells[1, 3] = "Account Name(s)";
                    xlWorkSheet.Cells[1, 4] = "Municipality";
                    xlWorkSheet.Cells[1, 5] = "Description";
                    xlWorkSheet.Cells[1, 6] = "Amount";
                    xlWorkSheet.Cells[1, 7] = "RC Code";
                    xlWorkSheet.Cells[1, 8] = "Requested by";
                    xlWorkSheet.Cells[1, 9] = "Branch";
                    printselect.formprint.toolStripProgressBar1.Value += 15;

                    foreach (string unit in units)
                    {
                        results = dt.Select("Branch = '" + unit + "'");
                        count = results.Count();
                        totalcount += count;
                        xlWorkSheet.Cells[count3, 1] = "Requestor: " + unit + " - ";
                        range = xlWorkSheet.get_Range(xlWorkSheet.Cells[count3, 1], xlWorkSheet.Cells[count3, 5]);
                        range.Merge();
                        releaseObject(range);
                        xlWorkSheet.Cells[count3, 6] = count;
                        xlWorkSheet.Cells[count3, 7] = "Account(s)";
                        count3++;
                        foreach (DataRow row in results)
                        {
                            xlWorkSheet.Cells[count3, 1] = count4;
                            xlWorkSheet.Cells[count3, 2] = row["Date of Endorsement"];
                            xlWorkSheet.Cells[count3, 3] = row["Account Name(s)"];
                            xlWorkSheet.Cells[count3, 4] = row["Municipality"];
                            xlWorkSheet.Cells[count3, 5] = row["Description"];
                            xlWorkSheet.Cells[count3, 6] = row["Amount"];
                            if (Convert.ToString(row["Amount"]) != "")
                            {
                                total += (float)row["Amount"];
                            }
                            xlWorkSheet.Cells[count3, 8] = row["Requested By"];
                            xlWorkSheet.Cells[count3, 9] = row["Branch"];
                            count3++;
                            count4++;
                        }
                        xlWorkSheet.Cells[count3, 2] = "Total: ";
                        xlWorkSheet.Cells[count3, 6] = total;
                        count4 = 1;
                        count3++;
                        grandtotal += total;
                        total = 0;
                    }

                    xlWorkSheet.Cells[count3, 2] = "Total: ";
                    xlWorkSheet.Cells[count3, 6] = grandtotal;
                    xlWorkSheet.Cells[count3 + 1, 2] = "Total Account(s): ";
                    xlWorkSheet.Cells[count3 + 1, 6] = totalcount;
                    printselect.formprint.toolStripProgressBar1.Value += 45;

                    xlWorkBook.SaveAs("Rcbc " + t + "- " + batch + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                    xlWorkBook.Close(true, misValue, misValue);
                    xlApp.Quit();
                    printselect.formprint.toolStripProgressBar1.Value += 15;

                    releaseObject(books);
                    releaseObject(sheets);
                    releaseObject(xlWorkSheet);
                    releaseObject(xlWorkBook);
                    releaseObject(xlApp);
                    TryKillProcessByMainWindowHwnd(hWnd);
                    printselect.formprint.toolStripProgressBar1.Value = 100;
                }
                catch (Exception err4)
                {
                    xlWorkBook.Close(true, misValue, misValue);
                    xlApp.Quit();
                    releaseObject(range);
                    releaseObject(sheets);
                    releaseObject(books);
                    releaseObject(xlWorkSheet);
                    releaseObject(xlWorkBook);
                    releaseObject(xlApp);
                    TryKillProcessByMainWindowHwnd(hWnd);
                    throw new Exception(err4.ToString());
                }
            }
            else
            {
                try
                {
                    List<string> units2 = new List<string>();
                    dt = assm.gettabulardata(id, no);
                    printselect.formprint.toolStripProgressBar1.Value += 5;
                    int count3 = 2;
                    int count4 = 1;
                    int count = 0;
                    object namecount = 2;
                    object requestcount = 1;
                    int namecount2 = 0;
                    int requestcount2 = 1;
                    object namestring = "";
                    object requeststring = "";
                    float total = 0;
                    float grandtotal = 0;
                    int totalcount = 0;
                    string requestor = "";
                    string person = "";
                    string unitstr = "";
                    DataView sorted = new DataView();

                    distinct = dt.DefaultView.ToTable(true, "Requestor");
                    distinct.DefaultView.Sort = "Requestor DESC";
                    sorted = distinct.DefaultView;

                    for (int n = 0; sorted.Count > n; n++)
                    {
                        units.Add(Convert.ToString(sorted[n]["Requestor"]));
                    }

                    distinct = dt.DefaultView.ToTable(true, "Requestor");

                    for (int n = 0; distinct.Rows.Count > n; n++)
                    {
                        units2.Add(Convert.ToString(distinct.Rows[n]["Requestor"]));
                    }

                    distinct = dt.DefaultView.ToTable(true, "Name");
                    distinct.DefaultView.Sort = "Name DESC";
                    sorted = distinct.DefaultView;

                    for (int n = 0; sorted.Count > n; n++)
                    {
                        if (!Convert.ToString(sorted[n]["Name"]).Equals(""))
                        {
                            names.Add(Convert.ToString(sorted[n]["Name"]));
                        }
                    }

                    foreach (string unit in units)
                    {
                        string tempstring;
                        results = dt.Select("Requestor = '" + unit + "'", "Municipality ASC");
                        tempstring = unit.Replace("BRANCH", "");
                        tempstring = tempstring.Replace("CENTER", "");
                        tempstring = tempstring.Replace("BUSINESS", "");
                        tempstring = tempstring.Replace("RCBC", "");
                        requeststring = validator.StringCleaner(tempstring) + "";
                        sheets.Add(misValue, misValue, 1, misValue);
                        xlWorkSheet = (Excel.Worksheet)sheets.get_Item(requestcount);
                        xlWorkSheet = (Excel.Worksheet)xlWorkBook.ActiveSheet;
                        xlWorkSheet.Name = requeststring.ToString();
                        xlWorkSheet.Cells[1, 1] = "Assign No.";
                        xlWorkSheet.Cells[1, 2] = "RCBC C.I.";
                        xlWorkSheet.Cells[1, 3] = "Account Name(s)";
                        xlWorkSheet.Cells[1, 4] = "Address";
                        xlWorkSheet.Cells[1, 5] = "Municipality";
                        xlWorkSheet.Cells[1, 6] = "Requestor";
                        xlWorkSheet.Cells[1, 7] = "Date of Endorsement";
                        xlWorkSheet.Cells[1, 8] = "Date of Submission";

                        foreach (DataRow row in results)
                        {
                            if (Convert.ToString(row["Amount"]) != "")
                            {
                                unitstr = row["Requestor"].ToString();
                                person = row["Person"].ToString();

                                if (person != "")
                                {
                                    requestor = unitstr + "/" + person;
                                }
                                else
                                {
                                    requestor = unitstr;
                                }

                                xlWorkSheet.Cells[count3, 1] = count4;
                                xlWorkSheet.Cells[count3, 2] = row["RCBC C.I."];
                                xlWorkSheet.Cells[count3, 3] = row["Account Name(s)"];
                                xlWorkSheet.Cells[count3, 4] = row["Address"];
                                xlWorkSheet.Cells[count3, 5] = row["Municipality"];
                                xlWorkSheet.Cells[count3, 6] = requestor;
                                xlWorkSheet.Cells[count3, 7] = row["Date of Endorsement"];
                                xlWorkSheet.Cells[count3, 8] = row["Date of Submission"];
                                count3++;
                                count4++;
                            }
                        }

                        count4 = 1;
                        count3 = 2;
                        requestcount2++;
                        requestcount = requestcount2;
                    }
                    printselect.formprint.toolStripProgressBar1.Value += 15;

                    namecount2 = requestcount2 + 1;
                    namecount = namecount2;
                    foreach (string name in names)
                    {
                        results = dt.Select("Name = '" + name + "'", "Municipality ASC");
                        string tempstring;
                        tempstring = name.Replace("BRANCH", "");
                        tempstring = tempstring.Replace("CENTER", "");
                        tempstring = tempstring.Replace("BUSINESS", "");
                        tempstring = tempstring.Replace("RCBC", "");
                        namestring = validator.StringCleaner(tempstring) + "";
                        sheets.Add(misValue, misValue, 1, misValue);
                        xlWorkSheet = (Excel.Worksheet)sheets.get_Item(namecount);
                        xlWorkSheet = (Excel.Worksheet)xlWorkBook.ActiveSheet;
                        xlWorkSheet.Name = namestring.ToString();

                        xlWorkSheet.Cells[1, 1] = "Assign No.";
                        xlWorkSheet.Cells[1, 2] = "RCBC C.I.";
                        xlWorkSheet.Cells[1, 3] = "Account Name(s)";
                        xlWorkSheet.Cells[1, 4] = "Address";
                        xlWorkSheet.Cells[1, 5] = "Municipality";
                        xlWorkSheet.Cells[1, 6] = "Requestor";
                        xlWorkSheet.Cells[1, 7] = "Date of Endorsement";
                        xlWorkSheet.Cells[1, 8] = "Date of Submission";

                        foreach (DataRow row in results)
                        {
                            unitstr = row["Requestor"].ToString();
                            person = row["Person"].ToString();

                            if (person != "")
                            {
                                requestor = unitstr + "/" + person;
                            }
                            else
                            {
                                requestor = unitstr;
                            }

                            xlWorkSheet.Cells[count3, 1] = count4;
                            xlWorkSheet.Cells[count3, 2] = row["RCBC C.I."];
                            xlWorkSheet.Cells[count3, 3] = row["Account Name(s)"];
                            xlWorkSheet.Cells[count3, 4] = row["Address"];
                            xlWorkSheet.Cells[count3, 5] = row["Municipality"];
                            xlWorkSheet.Cells[count3, 6] = requestor;
                            xlWorkSheet.Cells[count3, 7] = row["Date of Endorsement"];
                            xlWorkSheet.Cells[count3, 8] = row["Date of Submission"];
                            count3++;
                            count4++;
                        }

                        count3 = 2;
                        count4 = 1;
                        namecount2++;
                        namecount = namecount2;
                    }

                    printselect.formprint.toolStripProgressBar1.Value += 15;

                    namecount2 += 2;
                    namecount = namecount2;
                    sheets.Add(misValue, misValue, 1, misValue);
                    xlWorkSheet = (Excel.Worksheet)sheets.get_Item(namecount);
                    xlWorkSheet = (Excel.Worksheet)xlWorkBook.ActiveSheet;
                    xlWorkSheet.Name = "sorted";

                    xlWorkSheet.Cells[1, 1] = "Assign No.";
                    xlWorkSheet.Cells[1, 2] = "RCBC C.I.";
                    xlWorkSheet.Cells[1, 3] = "Account Name(s)";
                    xlWorkSheet.Cells[1, 4] = "Address";
                    xlWorkSheet.Cells[1, 5] = "Municipality";
                    xlWorkSheet.Cells[1, 6] = "Requestor";
                    xlWorkSheet.Cells[1, 7] = "Date of Endorsement";
                    xlWorkSheet.Cells[1, 8] = "Date of Submission";

                    distinct = dt;
                    distinct.DefaultView.Sort = "Municipality ASC";
                    sorted = distinct.DefaultView;

                    for (int i = 0; sorted.Count > i; i++)
                    {
                        unitstr = sorted[i]["Requestor"].ToString();
                        person = sorted[i]["Person"].ToString();

                        if (person != "")
                        {
                            requestor = unitstr + "/" + person;
                        }
                        else
                        {
                            requestor = unitstr;
                        }

                        xlWorkSheet.Cells[count3, 1] = count4;
                        xlWorkSheet.Cells[count3, 2] = sorted[i]["RCBC C.I."];
                        xlWorkSheet.Cells[count3, 3] = sorted[i]["Account Name(s)"];
                        xlWorkSheet.Cells[count3, 4] = sorted[i]["Address"];
                        xlWorkSheet.Cells[count3, 5] = sorted[i]["Municipality"];
                        xlWorkSheet.Cells[count3, 6] = requestor;
                        xlWorkSheet.Cells[count3, 7] = sorted[i]["Date of Endorsement"];
                        xlWorkSheet.Cells[count3, 8] = sorted[i]["Date of Submission"];
                        count3++;
                        count4++;
                    }

                    count3 = 2;
                    count4 = 1;
                    printselect.formprint.toolStripProgressBar1.Value += 10;

                    namecount2++;
                    namecount = namecount2;
                    sheets.Add(misValue, misValue, 1, misValue);
                    xlWorkSheet = (Excel.Worksheet)sheets.get_Item(namecount2);
                    xlWorkSheet = (Excel.Worksheet)xlWorkBook.ActiveSheet;
                    xlWorkSheet.Name = "RETRIEVED." + batch;
                    xlWorkSheet.Cells[1, 1] = "Assign No.";
                    xlWorkSheet.Cells[1, 2] = "RCBC C.I.";
                    xlWorkSheet.Cells[1, 3] = "Account Name(s)";
                    xlWorkSheet.Cells[1, 4] = "Address";
                    xlWorkSheet.Cells[1, 5] = "Municipality";
                    xlWorkSheet.Cells[1, 6] = "Requestor";
                    xlWorkSheet.Cells[1, 7] = "Date of Endorsement";
                    xlWorkSheet.Cells[1, 8] = "Date of Submission";

                    foreach (string unit in units2)
                    {
                        results = dt.Select("Requestor = '" + unit + "'", "Municipality ASC");
                        count = results.Count();
                        totalcount += count;
                        xlWorkSheet.Cells[count3, 1] = "Requestor: " + unit + " - ";
                        range = xlWorkSheet.get_Range(xlWorkSheet.Cells[count3, 1], xlWorkSheet.Cells[count3, 5]);
                        range.Merge();
                        releaseObject(range);
                        xlWorkSheet.Cells[count3, 6] = count;
                        xlWorkSheet.Cells[count3, 7] = "Account(s)";
                        count3++;
                        foreach (DataRow row in results)
                        {
                            if (Convert.ToString(row["Amount"]) != "")
                            {
                                unitstr = row["Requestor"].ToString();
                                person = row["Person"].ToString();

                                if (person != "")
                                {
                                    requestor = unitstr + "/" + person;
                                }
                                else
                                {
                                    requestor = unitstr;
                                }

                                xlWorkSheet.Cells[count3, 1] = count4;
                                xlWorkSheet.Cells[count3, 2] = row["RCBC C.I."];
                                xlWorkSheet.Cells[count3, 3] = row["Account Name(s)"];
                                xlWorkSheet.Cells[count3, 4] = row["Address"];
                                xlWorkSheet.Cells[count3, 5] = row["Municipality"];
                                xlWorkSheet.Cells[count3, 6] = requestor;
                                xlWorkSheet.Cells[count3, 7] = row["Date of Endorsement"];
                                xlWorkSheet.Cells[count3, 8] = row["Date of Submission"];
                                total += (float)row["Amount"];
                                count3++;
                                count4++;
                            }
                            else
                            {
                                totalcount--;
                            }
                        }
                        xlWorkSheet.Cells[count3, 1] = "S/A#: ";
                        xlWorkSheet.Cells[count3 + 1, 1] = "Amount: ";
                        xlWorkSheet.Cells[count3 + 1, 2] = total;
                        count4 = 1;
                        count3 += 2;
                        grandtotal += total;
                        total = 0;
                    }

                    xlWorkSheet.Cells[count3, 2] = "Total Account(s): ";
                    xlWorkSheet.Cells[count3, 6] = totalcount;
                    xlWorkSheet.Cells[count3 + 1, 2] = "Total Amount: ";
                    xlWorkSheet.Cells[count3 + 1, 6] = grandtotal;
                    printselect.formprint.toolStripProgressBar1.Value += 20;
                    xlWorkSheet.Cells[count3 + 2, 1] = "Accounts with incomplete address and cannot be located";
                    range = xlWorkSheet.get_Range(xlWorkSheet.Cells[count3 + 2, 1], xlWorkSheet.Cells[count3 + 2, 8]);
                    range.Merge();
                    releaseObject(range);
                    count3 += 3;
                    count4 = 1;
                    foreach (DataRow row in dt.Rows)
                    {
                        if (Convert.ToString(row["Amount"]) == "")
                        {
                            unitstr = row["Requestor"].ToString();
                            person = row["Person"].ToString();

                            if (person != "")
                            {
                                requestor = unitstr + "/" + person;
                            }
                            else
                            {
                                requestor = unitstr;
                            }

                            xlWorkSheet.Cells[count3, 1] = count4;
                            xlWorkSheet.Cells[count3, 2] = row["RCBC C.I."];
                            xlWorkSheet.Cells[count3, 3] = row["Account Name(s)"];
                            xlWorkSheet.Cells[count3, 4] = row["Address"];
                            xlWorkSheet.Cells[count3, 5] = row["Municipality"];
                            xlWorkSheet.Cells[count3, 6] = requestor;
                            xlWorkSheet.Cells[count3, 7] = row["Date of Endorsement"];
                            xlWorkSheet.Cells[count3, 8] = row["Date of Submission"];
                            count3++;
                            count4++;
                        }
                    }

                    printselect.formprint.toolStripProgressBar1.Value += 15;

                    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets["Sheet1"];
                    xlWorkSheet.Select(misValue);
                    xlWorkSheet = (Excel.Worksheet)xlWorkBook.ActiveSheet;
                    xlWorkSheet.Delete();
                    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets["Sheet2"];
                    xlWorkSheet.Select(misValue);
                    xlWorkSheet = (Excel.Worksheet)xlWorkBook.ActiveSheet;
                    xlWorkSheet.Delete();
                    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets["Sheet3"];
                    xlWorkSheet.Select(misValue);
                    xlWorkSheet = (Excel.Worksheet)xlWorkBook.ActiveSheet;
                    xlWorkSheet.Delete();
                    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets["RETRIEVED." + batch];
                    xlWorkSheet.Select(misValue);
                    xlWorkSheet = (Excel.Worksheet)xlWorkBook.ActiveSheet;
                    printselect.formprint.toolStripProgressBar1.Value += 5;

                    xlWorkBook.SaveAs("rcbc." + t + "." + batch + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                    xlWorkBook.Close(true, misValue, misValue);
                    xlApp.Quit();
                    printselect.formprint.toolStripProgressBar1.Value += 10;

                    releaseObject(books);
                    releaseObject(sheets);
                    releaseObject(xlWorkSheet);
                    releaseObject(xlWorkBook);
                    releaseObject(xlApp);
                    TryKillProcessByMainWindowHwnd(hWnd);

                    printselect.formprint.toolStripProgressBar1.Value = 100;
                }
                catch (Exception err4)
                {
                    xlWorkBook.Close(true, misValue, misValue);
                    xlApp.Quit();
                    releaseObject(range);
                    releaseObject(books);
                    releaseObject(sheets);
                    releaseObject(xlWorkSheet);
                    releaseObject(xlWorkBook);
                    releaseObject(xlApp);
                    TryKillProcessByMainWindowHwnd(hWnd);
                    throw new Exception(err4.ToString());
                }
            }
        }

        public void PrintCV(DataGridView payDataDisplay)
        {
            try
            {
                DataTable payment = new DataTable();
                payment = payDataDisplay.DataSource as DataTable;
                int count = payDataDisplay.RowCount - 1;
                DataRow dataRow = payment.Rows[0];
                int batch = Convert.ToInt32(dataRow["Batch No."]);
                int currow = 0;
                string date = Convert.ToString(payment.Rows[0]["Date"]);
                date = date.Replace("/", "");

                xlApp = new Excel.ApplicationClass();
                books = xlApp.Workbooks;
                xlWorkBook = books.Add(misValue);
                sheets = xlWorkBook.Sheets;
                xlWorkSheet = (Excel.Worksheet)sheets.get_Item(1);
                int j = 0;
                hWnd = xlApp.Application.Hwnd;
                float taxes = 0;
                float net = 0;

                xlWorkSheet.Cells[1, 3] = "IHUB Corporate Business Services, Inc.";
                xlWorkSheet.Cells[2, 3] = "203 Cityland III cor. V.A. Rufino & Esteban Sts.";
                xlWorkSheet.Cells[3, 3] = "Legaspi Village, Makati City";
                xlWorkSheet.Cells[4, 3] = "CASH VOUCHER";
                xlWorkSheet.Cells[5, 1] = "Date";
                xlWorkSheet.Cells[5, 2] = payment.Rows[0]["Date"];
                xlWorkSheet.Cells[6, 1] = "Payee";
                xlWorkSheet.Cells[6, 2] = payment.Rows[0]["Name"];
                xlWorkSheet.Cells[7, 1] = "Address";
                xlWorkSheet.Cells[7, 2] = payment.Rows[0]["Employee Address"];
                xlWorkSheet.Cells[8, 2] = "Particulars";
                xlWorkSheet.Cells[8, 4] = "Amount";
                xlWorkSheet.Cells[9, 1] = "Payment for:";
                xlWorkSheet.Cells[10, 1] = count + " Completed Accounts-Batch" + batch;

                for (j = 0; payment.Rows.Count > j; j++)
                {
                    xlWorkSheet.Cells[12 + j, 1] = payment.Rows[j]["Assignment Location"];
                    xlWorkSheet.Cells[12 + j, 3] = 1;
                    xlWorkSheet.Cells[12 + j, 4] = payment.Rows[j]["Assignment Pay"];
                    xlWorkSheet.Cells[12 + j, 5] = payment.Rows[j]["Assignment Pay"];
                    currow += j;
                }

                taxes = (float)payment.Rows[0]["Withholding Tax"];
                net = (float)payment.Rows[0]["Net Amount Payable"];
                xlWorkSheet.Cells[12 + currow + 2, 1] = "Total";
                xlWorkSheet.Cells[12 + currow + 2, 5] = payment.Rows[0]["Gross Amount Payable"];
                xlWorkSheet.Cells[12 + currow + 3, 2] = "2% w/tax";
                xlWorkSheet.Cells[12 + currow + 3, 5] = taxes.ToString("0.00");
                xlWorkSheet.Cells[12 + currow + 4, 2] = "Cash Advances";
                xlWorkSheet.Cells[12 + currow + 4, 5] = payment.Rows[0]["Cash Advances"];
                xlWorkSheet.Cells[12 + currow + 6, 2] = "Total";
                xlWorkSheet.Cells[12 + currow + 6, 5] = net.ToString("0.00");
                xlWorkSheet.Cells[12 + currow + 7, 1] = "Approved By:";
                xlWorkSheet.Cells[12 + currow + 7, 4] = "Received By:";
                xlWorkSheet.Cells[12 + currow + 8, 1] = "Vilma M. Constantino";
                xlWorkSheet.Cells[12 + currow + 8, 4] = payment.Rows[0]["Name"];
                xlWorkSheet.Cells[12 + currow + 9, 1] = "Treasurer";
                xlWorkSheet.Cells[12 + currow + 9, 4] = "Date: ";

                xlWorkBook.SaveAs("cashvoucher-" + payment.Rows[0]["Name"] + "-" + date + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();

                releaseObject(sheets);
                releaseObject(books);
                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);
                TryKillProcessByMainWindowHwnd(hWnd);
            }
            catch (Exception err5)
            {
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();
                releaseObject(sheets);
                releaseObject(books);
                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);
                TryKillProcessByMainWindowHwnd(hWnd);
                throw new Exception(err5.ToString());
            }
        }

        public void PrintPayroll(DataGridView payDataDisplay, DateTime listbegin, DateTime listend)
        {
            try
            {
                DataTable payroll = new DataTable();
                payroll = payDataDisplay.DataSource as DataTable;
                xlApp = new Excel.ApplicationClass();
                books = xlApp.Workbooks;
                xlWorkBook = books.Add(misValue);
                sheets = xlWorkBook.Sheets;
                xlWorkSheet = (Excel.Worksheet)sheets.get_Item(1);
                hWnd = xlApp.Application.Hwnd;
                int i = 0;
                string datestart = listbegin.ToLongDateString();
                string dateend = listend.ToLongDateString();
                int count = 0;
                float grosstotal = 0;
                float nettotal = 0;
                float tempgross = 0;
                float tempnet = 0;
                float temptax = 0;

                xlWorkSheet.Cells[1, 1] = "Name";
                xlWorkSheet.Cells[1, 2] = "Tax Identification Number";
                xlWorkSheet.Cells[1, 3] = "Gross Amount Payable";
                xlWorkSheet.Cells[1, 4] = "Cash Advances";
                xlWorkSheet.Cells[1, 5] = "Withholding Tax";
                xlWorkSheet.Cells[1, 6] = "Net Amount Payable";

                for (i = 0; i <= payroll.Rows.Count - 1; i++)
                {
                    tempgross = (float)payroll.Rows[i][2];
                    temptax = (float)payroll.Rows[i][4];
                    tempnet = (float)payroll.Rows[i][5];
                    xlWorkSheet.Cells[i + 2, 1] = payroll.Rows[i][0];
                    xlWorkSheet.Cells[i + 2, 2] = payroll.Rows[i][1];
                    xlWorkSheet.Cells[i + 2, 3] = tempgross.ToString("0.00");
                    xlWorkSheet.Cells[i + 2, 4] = payroll.Rows[i][3];
                    xlWorkSheet.Cells[i + 2, 5] = temptax.ToString("0.00");
                    xlWorkSheet.Cells[i + 2, 6] = tempnet.ToString("0.00");
                    grosstotal += tempgross;
                    nettotal += tempnet;
                    count++;
                }

                xlWorkSheet.Cells[count + 2, 1] = "Total";
                xlWorkSheet.Cells[count + 2, 3] = grosstotal.ToString("0.00");
                xlWorkSheet.Cells[count + 2, 6] = nettotal.ToString("0.00");

                xlWorkBook.SaveAs("payroll-" + datestart + "-" + dateend + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();

                releaseObject(sheets);
                releaseObject(xlWorkSheet);
                releaseObject(books);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);
                TryKillProcessByMainWindowHwnd(hWnd);
            }
            catch (Exception err5)
            {
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();
                releaseObject(sheets);
                releaseObject(xlWorkSheet);
                releaseObject(books);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);
                TryKillProcessByMainWindowHwnd(hWnd);
                throw new Exception(err5.ToString());
            }
        }

        public void PrintBIR(DataGridView payDataDisplay, int biryear)
        {
            try
            {
                DataTable bir = new DataTable();
                bir = payDataDisplay.DataSource as DataTable;
                xlApp = new Excel.ApplicationClass();
                books = xlApp.Workbooks;
                xlWorkBook = books.Add(misValue);
                sheets = xlWorkBook.Sheets;
                xlWorkSheet = (Excel.Worksheet)sheets.get_Item(1);
                hWnd = xlApp.Application.Hwnd;
                int i = 0;
                float grosstotal = 0;
                float nettotal = 0;
                int count = 0;
                float tempgross = 0;
                float temptax = 0;
                float tempnet = 0;

                xlWorkSheet.Cells[1, 1] = "Month";
                xlWorkSheet.Cells[1, 2] = "Tax Identification Number";
                xlWorkSheet.Cells[1, 3] = "Name";
                xlWorkSheet.Cells[1, 4] = "ATC Code";
                xlWorkSheet.Cells[1, 5] = "Nature of Payment";
                xlWorkSheet.Cells[1, 6] = "Gross Amount Payable";
                xlWorkSheet.Cells[1, 7] = "Withholding Tax";
                xlWorkSheet.Cells[1, 8] = "Net Amount Payable";

                for (i = 0; i <= bir.Rows.Count - 1; i++)
                {
                    tempgross = (float)bir.Rows[i][5];
                    temptax = (float)bir.Rows[i][6];
                    tempnet = (float)bir.Rows[i][7];
                    xlWorkSheet.Cells[i + 2, 1] = bir.Rows[i][0];
                    xlWorkSheet.Cells[i + 2, 2] = bir.Rows[i][1];
                    xlWorkSheet.Cells[i + 2, 3] = bir.Rows[i][2];
                    xlWorkSheet.Cells[i + 2, 4] = bir.Rows[i][3];
                    xlWorkSheet.Cells[i + 2, 5] = bir.Rows[i][4];
                    xlWorkSheet.Cells[i + 2, 6] = tempgross.ToString("0.00");
                    xlWorkSheet.Cells[i + 2, 7] = temptax.ToString("0.00");
                    xlWorkSheet.Cells[i + 2, 8] = tempnet.ToString("0.00");
                    grosstotal += tempgross;
                    nettotal += tempnet;
                    count++;
                }

                xlWorkSheet.Cells[count + 2, 2] = "Total";
                xlWorkSheet.Cells[count + 2, 6] = grosstotal.ToString("0.00");
                xlWorkSheet.Cells[count + 2, 8] = nettotal.ToString("0.00");

                xlWorkBook.SaveAs("bir-" + payDataDisplay[0, 0].Value + "-" + biryear + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();

                releaseObject(sheets);
                releaseObject(books);
                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);
                TryKillProcessByMainWindowHwnd(hWnd);
            }
            catch (Exception err5)
            {
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();
                releaseObject(sheets);
                releaseObject(books);
                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);
                TryKillProcessByMainWindowHwnd(hWnd);
                throw new Exception(err5.ToString());
            }
        }

        public void PrintBIRYear(DataGridView payDataDisplay, int biryear)
        {
            try
            {
                DataTable distinct = new DataTable();
                DataRow[] result;
                List<string> months = new List<string>();
                DataTable bir = new DataTable();
                bir = payDataDisplay.DataSource as DataTable;
                xlApp = new Excel.ApplicationClass();
                books = xlApp.Workbooks;
                xlWorkBook = books.Add(misValue);
                sheets = xlWorkBook.Sheets;
                xlWorkSheet = (Excel.Worksheet)sheets.get_Item(1);
                hWnd = xlApp.Application.Hwnd;
                float grosstotal = 0;
                float nettotal = 0;
                int count = 0;
                float tempgross = 0;
                float temptax = 0;
                float tempnet = 0;
                int monthcount = 1;
                object monthcount2 = 1;

                distinct = bir.DefaultView.ToTable(true, "Month");

                foreach (DataRow row in distinct.Rows)
                {
                    months.Add(Convert.ToString(row["Month"]));
                }

                foreach (string month in months)
                {
                    string tempmonth = month;
                    result = bir.Select("Month = '" + month + "'");
                    sheets.Add(misValue, misValue, 1, misValue);
                    xlWorkSheet = (Excel.Worksheet)sheets.get_Item(monthcount2);
                    xlWorkSheet = (Excel.Worksheet)xlWorkBook.ActiveSheet;
                    xlWorkSheet.Name = tempmonth + "";
                    xlWorkSheet.Cells[1, 1] = "Month";
                    xlWorkSheet.Cells[1, 2] = "Tax Identification Number";
                    xlWorkSheet.Cells[1, 3] = "Name";
                    xlWorkSheet.Cells[1, 4] = "ATC Code";
                    xlWorkSheet.Cells[1, 5] = "Nature of Payment";
                    xlWorkSheet.Cells[1, 6] = "Gross Amount Payable";
                    xlWorkSheet.Cells[1, 7] = "Withholding Tax";
                    xlWorkSheet.Cells[1, 8] = "Net Amount Payable";

                    foreach (DataRow row in result)
                    {
                        tempgross = (float)row[5];
                        temptax = (float)row[6];
                        tempnet = (float)row[7];
                        xlWorkSheet.Cells[count + 2, 1] = row[0];
                        xlWorkSheet.Cells[count + 2, 2] = row[1];
                        xlWorkSheet.Cells[count + 2, 3] = row[2];
                        xlWorkSheet.Cells[count + 2, 4] = row[3];
                        xlWorkSheet.Cells[count + 2, 5] = row[4];
                        xlWorkSheet.Cells[count + 2, 6] = tempgross.ToString("0.00");
                        xlWorkSheet.Cells[count + 2, 7] = temptax.ToString("0.00");
                        xlWorkSheet.Cells[count + 2, 8] = tempnet.ToString("0.00");
                        grosstotal += tempgross;
                        nettotal += tempnet;
                        count++;
                    }

                    xlWorkSheet.Cells[count + 2, 2] = "Total";
                    xlWorkSheet.Cells[count + 2, 6] = grosstotal.ToString("0.00");
                    xlWorkSheet.Cells[count + 2, 8] = nettotal.ToString("0.00");
                    nettotal = 0;
                    grosstotal = 0;
                    count = 0;
                    monthcount++;
                    monthcount2 = monthcount;
                }

                sheets.Add(misValue, misValue, 1, misValue);
                xlWorkSheet = (Excel.Worksheet)sheets.get_Item(monthcount2);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.ActiveSheet;
                xlWorkSheet.Name = "All";
                xlWorkSheet.Cells[1, 1] = "Month";
                xlWorkSheet.Cells[1, 2] = "Tax Identification Number";
                xlWorkSheet.Cells[1, 3] = "Name";
                xlWorkSheet.Cells[1, 4] = "ATC Code";
                xlWorkSheet.Cells[1, 5] = "Nature of Payment";
                xlWorkSheet.Cells[1, 6] = "Gross Amount Payable";
                xlWorkSheet.Cells[1, 7] = "Withholding Tax";
                xlWorkSheet.Cells[1, 8] = "Net Amount Payable";

                foreach (DataRow row in bir.Rows)
                {
                    tempgross = (float)row[5];
                    temptax = (float)row[6];
                    tempnet = (float)row[7];
                    xlWorkSheet.Cells[count + 2, 1] = row[0];
                    xlWorkSheet.Cells[count + 2, 2] = row[1];
                    xlWorkSheet.Cells[count + 2, 3] = row[2];
                    xlWorkSheet.Cells[count + 2, 4] = row[3];
                    xlWorkSheet.Cells[count + 2, 5] = row[4];
                    xlWorkSheet.Cells[count + 2, 6] = tempgross.ToString("0.00");
                    xlWorkSheet.Cells[count + 2, 7] = temptax.ToString("0.00");
                    xlWorkSheet.Cells[count + 2, 8] = tempnet.ToString("0.00");
                    grosstotal += tempgross;
                    nettotal += tempnet;
                    count++;
                }

                xlWorkSheet.Cells[count + 2, 2] = "Total";
                xlWorkSheet.Cells[count + 2, 6] = grosstotal.ToString("0.00");
                xlWorkSheet.Cells[count + 2, 8] = nettotal.ToString("0.00");

                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets["Sheet1"];
                xlWorkSheet.Select(misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.ActiveSheet;
                xlWorkSheet.Delete();
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets["Sheet2"];
                xlWorkSheet.Select(misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.ActiveSheet;
                xlWorkSheet.Delete();
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets["Sheet3"];
                xlWorkSheet.Select(misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.ActiveSheet;
                xlWorkSheet.Delete();
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets["All"];
                xlWorkSheet.Select(misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.ActiveSheet;

                xlWorkBook.SaveAs("bir-" + biryear + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();

                releaseObject(sheets);
                releaseObject(books);
                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);
                TryKillProcessByMainWindowHwnd(hWnd);
            }
            catch (Exception err5)
            {
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();
                releaseObject(sheets);
                releaseObject(books);
                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);
                TryKillProcessByMainWindowHwnd(hWnd);
                throw new Exception(err5.ToString());
            }
        }
    }
}